package com.Alan.dao.supplier;

import com.Alan.dao.BaseDao;
import com.Alan.pojo.Supplier;
import com.Alan.util.UtilsAlan;
import com.mysql.cj.util.StringUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SupplierDaoImpl implements SupplierDao{
    @Override
    public int getSupCount(Connection connection, String supName, String contacts) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int count = 0;
        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select count(1) supCount from supplier");
            List<Object> list = new ArrayList<Object>();

            if (!StringUtils.isNullOrEmpty(supName)){
                sql.append(" where supName like ?");
                list.add("%" + supName + "%");
            }

            if (!StringUtils.isNullOrEmpty(contacts)){
                if (!StringUtils.isNullOrEmpty(supName)){
                    sql.append(" and");
                }else {
                    sql.append(" where");
                }
                sql.append(" contacts like ?");
                list.add("%" + contacts + "%");
            }

            Object[] params = list.toArray();
            System.out.println("提示根据条件查询Supplier总数SQL=" + sql.toString());

            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);
                if (resultSet.next()){
                    count = resultSet.getInt("supCount");
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:SupplierDaoImpl -> getSupCount");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }

        return count;
    }

    @Override
    public List<Supplier> getSupList(Connection connection, String supName, String contacts, int startRow, int pageRows) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Supplier> listSup = new ArrayList<Supplier>();

        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select * from supplier ");
            List<Object> list = new ArrayList<>();

            if (!StringUtils.isNullOrEmpty(supName)){
                sql.append(" where supName like ?");
                list.add("%" + supName + "%");
            }
            if (!StringUtils.isNullOrEmpty(contacts)){
                if (!StringUtils.isNullOrEmpty(supName)){
                    sql.append(" and");
                }else {
                    sql.append(" where");
                }
                sql.append(" contacts like ?");
                list.add("%" + contacts + "%");
            }
            list.add(startRow);
            list.add(pageRows);
            sql.append(" order by id limit ?, ?");
            Object[] params = list.toArray();
            System.out.println("获取查询的供应商列表的SQL语句 === " + sql.toString());


            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);
                while (resultSet.next()){
                    Supplier supplier = new Supplier();
                    supplier.setId(resultSet.getInt("id"));
                    supplier.setSupCode(resultSet.getString("supCode"));
                    supplier.setSupName(resultSet.getString("supName"));
                    supplier.setContacts(resultSet.getString("contacts"));
                    supplier.setGender(resultSet.getString("gender"));
                    supplier.setAge(resultSet.getInt("age"));
                    supplier.setAddress(resultSet.getString("address"));
                    supplier.setPhone(resultSet.getString("phone"));
                    supplier.setDescribe(resultSet.getString("describe"));
                    listSup.add(supplier);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:SupplierDaoImpl -> getSupList");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }

        return listSup;
    }

    @Override
    public int supExist(Connection connection, String supCode) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int existRows = 0;

        if (connection != null){
            String sql = "select count(1) existRows from supplier where supCode = ?;";
            Object[] params = {supCode};

            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);
                if (resultSet.next()){
                    existRows = resultSet.getInt("existRows");
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:SupplierDaoImpl -> supExist");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }

        return existRows;
    }

    @Override
    public Supplier getSupInformation(Connection connection, int supId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Supplier supplier = null;

        if (connection != null){
            String sql = "select * from supplier where id = ?;";
            Object[] params = {supId};

            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);
                if (resultSet.next()){
                    supplier = new Supplier();
                    supplier.setId(resultSet.getInt("id"));
                    supplier.setSupCode(resultSet.getString("supCode"));
                    supplier.setSupName(resultSet.getString("supName"));
                    supplier.setContacts(resultSet.getString("contacts"));
                    supplier.setGender(resultSet.getString("gender"));
                    supplier.setAge(resultSet.getInt("age"));
                    supplier.setAddress(resultSet.getString("address"));
                    supplier.setPhone(resultSet.getString("phone"));
                    supplier.setDescribe(resultSet.getString("describe"));
                    supplier.setCreatedByUserCode(resultSet.getString("createdByUserCode"));
                    supplier.setCreatedByUserName(resultSet.getString("createdByUserName"));
                    supplier.setCreationDate(resultSet.getDate("creationDate"));
                    supplier.setModifyByUserCode(resultSet.getString("modifyByUserCode"));
                    supplier.setModifyByUserName(resultSet.getString("modifyByUserName"));
                    supplier.setModifyDate(resultSet.getDate("modifyDate"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:SupplierDaoImpl -> getSupInformation");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }

        return supplier;
    }

    @Override
    public int updateSup(Connection connection, Supplier supplier) {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;

        if (connection != null){
            String sql = "update supplier set supName = ?, contacts = ?, gender = ?, age = ?, address = ?, phone = ?, " +
                    "`describe` = ?, modifyByUserCode = ?, modifyByUserName = ?, modifyDate = ? where id = ?;";
            Object[] params = {
                    supplier.getSupName(), supplier.getContacts(), supplier.getGender(), supplier.getAge(),
                    supplier.getAddress(), supplier.getPhone(), supplier.getDescribe(), supplier.getModifyByUserCode(),
                    supplier.getModifyByUserName(), supplier.getModifyDate(), supplier.getId()
            };

            System.out.println("修改供应商的SQL === " + sql);

            try {
                updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

            BaseDao.closeResource(null, preparedStatement, null);
        }

        return updateRows;
    }

    @Override
    public int addSup(Connection connection, Supplier supplier) {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;

        if (connection != null){
            String sql = "insert into supplier (" +
                    "supCode, supName, contacts, gender, age, address, phone, `describe`, " +
                    "createdByUserCode, createdByUserName, creationDate) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
            Object[] params = {
                    supplier.getSupCode(), supplier.getSupName(), supplier.getContacts(), supplier.getGender(),
                    supplier.getAge(), supplier.getAddress(), supplier.getPhone(), supplier.getDescribe(),
                    supplier.getCreatedByUserCode(), supplier.getCreatedByUserName(), supplier.getCreationDate()
            };

            System.out.println("添加供应商的SQL === " + sql);

            try {
                updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:SupplierDaoImpl -> addSup");
            }

            BaseDao.closeResource(null, preparedStatement, null);
        }

        return updateRows;
    }

    @Override
    public int deleteSup(Connection connection, int supId) {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;

        if (connection != null){
            String sql = "delete from supplier where id = ?;";
            Object[] params = {supId};

            try {
                updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:SupplierDaoImpl -> deleteSup");
            }

            BaseDao.closeResource(null, preparedStatement, null);
        }

        return updateRows;
    }

    @Override
    public List<Supplier> getSupNames(Connection connection) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Supplier> supList = new ArrayList<Supplier>();

        if (connection != null){
            String sql = "select id, supName from `supplier`";
            Object[] params = {};
            try {
                resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);
                while (resultSet.next()){
                    Supplier supplier = new Supplier();
                    supplier.setId(resultSet.getInt("id"));
                    supplier.setSupName(resultSet.getString("supName"));
                    supList.add(supplier);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("ERROR:SupplierDaoImpl -> getSupNames");
            }

            BaseDao.closeResource(null, preparedStatement, resultSet);
        }

        return supList;
    }


    @Test
    public void test(){
        SupplierDao supplierDao = new SupplierDaoImpl();
        /*int supCount = supplierDao.getSupCount(BaseDao.getConnection(), "重", "");
        System.out.println(supCount);*/

        /*List<Supplier> supList = supplierDao.getSupList(BaseDao.getConnection(), "", "", 0, 2);
        for (Supplier supplier : supList) {
            System.out.println(supplier.toString());
        }*/

        /*Supplier supInformation = supplierDao.getSupInformation(BaseDao.getConnection(), 1);
        System.out.println(supInformation.toString());*/

        /*Supplier sup = new Supplier();
        sup.setId(6);
        sup.setSupCode("66666");
        sup.setSupName("66666");
        sup.setContacts("某某");
        sup.setGender("男");
        sup.setAge(12);
        sup.setAddress("重庆");
        sup.setPhone("123456789");
        sup.setDescribe("描述");
        sup.setCreatedByUserCode("admin");
        sup.setCreatedByUserName("系统管理员");
        sup.setCreationDate(UtilsAlan.strToSqlDate("2021-08-08", false));
        sup.setModifyByUserCode("admin");
        sup.setModifyByUserName("系统管理员");
        sup.setModifyDate(UtilsAlan.strToSqlDate("2021-08-08", false));

        System.out.println(supplierDao.addSup(BaseDao.getConnection(), sup));

//        System.out.println(supplierDao.updateSup(BaseDao.getConnection(), sup));*/

        /*System.out.println(supplierDao.deleteSup(BaseDao.getConnection(), 6));*/

        /*int test = supplierDao.supExist(BaseDao.getConnection(), "test");
        System.out.println(test);*/

        /*List<Supplier> supNames = supplierDao.getSupNames(BaseDao.getConnection());
        for (Supplier supName : supNames) {
            System.out.println(supName.toString());
        }*/
    }
}
